from app.utils.mysql_util import get_connection
from app.sql.mysql_main import main_opera

def insert_shop(shop_data):
    try:
        sql = """INSERT INTO shop (
            platform_id, shop_id, shop_no, shop_name, account_id, 
            account_nick, province, city, district, address, 
            contact, zip, mobile, telno, remark
        ) VALUES (
            %(platform_id)s, %(shop_id)s, %(shop_no)s, %(shop_name)s, %(account_id)s,
            %(account_nick)s, %(province)s, %(city)s, %(district)s, %(address)s,
            %(contact)s, %(zip)s, %(mobile)s, %(telno)s, %(remark)s
        ) ON DUPLICATE KEY UPDATE
            shop_name = VALUES(shop_name),
            account_nick = VALUES(account_nick),
            address = VALUES(address),
            contact = VALUES(contact),
            mobile = VALUES(mobile),
            telno = VALUES(telno),
            remark = VALUES(remark)
        """
        main_opera(sql, shop_data, False)
    except Exception as e:
        raise e

def insert_logistics(logistics_data):
    try:
        sql = """INSERT INTO logistics (
            logistics_id, logistics_no, logistics_name, logistics_type,
            province, city, district, contact, telno, mobile, address,
            commission_ratio, send_type, prop1, prop2, prop3,
            is_disabled, group_name, abled_num, priority,
            is_support_cod, service_mask, bill_type, is_online,
            is_dtb_no_api, un_repeat_logistics_no, is_manual,
            logistics_regex, be_uncheck_regexp, remark,
            modified, created, zip
        ) VALUES (
            %(logistics_id)s, %(logistics_no)s, %(logistics_name)s, %(logistics_type)s,
            %(province)s, %(city)s, %(district)s, %(contact)s, %(telno)s, %(mobile)s, %(address)s,
            %(commission_ratio)s, %(send_type)s, %(prop1)s, %(prop2)s, %(prop3)s,
            %(is_disabled)s, %(group_name)s, %(abled_num)s, %(priority)s,
            %(is_support_cod)s, %(service_mask)s, %(bill_type)s, %(is_online)s,
            %(is_dtb_no_api)s, %(un_repeat_logistics_no)s, %(is_manual)s,
            %(logistics_regex)s, %(be_uncheck_regexp)s, %(remark)s,
            %(modified)s, %(created)s, %(zip)s
        ) ON DUPLICATE KEY UPDATE
            logistics_no = VALUES(logistics_no),
            logistics_name = VALUES(logistics_name),
            logistics_type = VALUES(logistics_type),
            province = VALUES(province),
            city = VALUES(city),
            district = VALUES(district),
            contact = VALUES(contact),
            telno = VALUES(telno),
            mobile = VALUES(mobile),
            address = VALUES(address),
            commission_ratio = VALUES(commission_ratio),
            send_type = VALUES(send_type),
            prop1 = VALUES(prop1),
            prop2 = VALUES(prop2),
            prop3 = VALUES(prop3),
            is_disabled = VALUES(is_disabled),
            group_name = VALUES(group_name),
            abled_num = VALUES(abled_num),
            priority = VALUES(priority),
            is_support_cod = VALUES(is_support_cod),
            service_mask = VALUES(service_mask),
            bill_type = VALUES(bill_type),
            is_online = VALUES(is_online),
            is_dtb_no_api = VALUES(is_dtb_no_api),
            un_repeat_logistics_no = VALUES(un_repeat_logistics_no),
            is_manual = VALUES(is_manual),
            logistics_regex = VALUES(logistics_regex),
            be_uncheck_regexp = VALUES(be_uncheck_regexp),
            remark = VALUES(remark),
            modified = VALUES(modified),
            zip = VALUES(zip)
        """
        main_opera(sql, logistics_data, False)
    except Exception as e:
        raise e

def insert_warehouse(warehouse_data):
    try:
        sql = """INSERT INTO ware_house (
            warehouse_no, name, warehouse_type, ext_warehouse_no, province,
            city, district, address, contact, telno, zip, mobile,
            is_defect, remark, prop1, prop2, warehouse_id, type,
            sub_type, api_key, api_object_id, tag, division_id,
            flag, shop_id, is_outer_stock, is_outer_goods,
            picker_num, coordinates_x, coordinates_y,
            match_warehouse_id, cod_logistics_id, is_disabled,
            priority, created, modified
        ) VALUES (
            %(warehouse_no)s, %(name)s, %(warehouse_type)s, %(ext_warehouse_no)s, %(province)s,
            %(city)s, %(district)s, %(address)s, %(contact)s, %(telno)s, %(zip)s, %(mobile)s,
            %(is_defect)s, %(remark)s, %(prop1)s, %(prop2)s, %(warehouse_id)s, %(type)s,
            %(sub_type)s, %(api_key)s, %(api_object_id)s, %(tag)s, %(division_id)s,
            %(flag)s, %(shop_id)s, %(is_outer_stock)s, %(is_outer_goods)s,
            %(picker_num)s, %(coordinates_x)s, %(coordinates_y)s,
            %(match_warehouse_id)s, %(cod_logistics_id)s, %(is_disabled)s,
            %(priority)s, %(created)s, %(modified)s
        ) ON DUPLICATE KEY UPDATE
            name = VALUES(name),
            warehouse_type = VALUES(warehouse_type),
            ext_warehouse_no = VALUES(ext_warehouse_no),
            province = VALUES(province),
            city = VALUES(city),
            district = VALUES(district),
            address = VALUES(address),
            contact = VALUES(contact),
            telno = VALUES(telno),
            zip = VALUES(zip),
            mobile = VALUES(mobile),
            is_defect = VALUES(is_defect),
            remark = VALUES(remark),
            prop1 = VALUES(prop1),
            prop2 = VALUES(prop2),
            type = VALUES(type),
            sub_type = VALUES(sub_type),
            api_key = VALUES(api_key),
            api_object_id = VALUES(api_object_id),
            tag = VALUES(tag),
            division_id = VALUES(division_id),
            flag = VALUES(flag),
            shop_id = VALUES(shop_id),
            is_outer_stock = VALUES(is_outer_stock),
            is_outer_goods = VALUES(is_outer_goods),
            picker_num = VALUES(picker_num),
            coordinates_x = VALUES(coordinates_x),
            coordinates_y = VALUES(coordinates_y),
            match_warehouse_id = VALUES(match_warehouse_id),
            cod_logistics_id = VALUES(cod_logistics_id),
            is_disabled = VALUES(is_disabled),
            priority = VALUES(priority),
            modified = VALUES(modified)
        """
        main_opera(sql, warehouse_data, False)
    except Exception as e:
        raise e

def insert_provider(provider_data):
    try:
        sql = """INSERT INTO provider (
            provider_no, provider_name, provider_group, contact,
            telno, mobile, address, email, zip
        ) VALUES (
            %(provider_no)s, %(provider_name)s, %(provider_group)s, %(contact)s,
            %(telno)s, %(mobile)s, %(address)s, %(email)s, %(zip)s
        ) ON DUPLICATE KEY UPDATE
            provider_name = VALUES(provider_name),
            contact = VALUES(contact),
            telno = VALUES(telno),
            mobile = VALUES(mobile),
            address = VALUES(address)
        """
        main_opera(sql, provider_data, False)
    except Exception as e:
        raise e

def insert_stockout_order(order_data):
        # 确保所有值都是字符串类型，避免类型转换问题
    for key in order_data:
            if order_data[key] is None:
                order_data[key] = ''
            elif isinstance(order_data[key], (list, dict)):
                order_data[key] = str(order_data[key])

    """插入出库单数据"""
    try:
          # SQL语句
        sql = """
            INSERT INTO stockout_orders (
                stockout_id, order_no, trade_id, warehouse_id, logistics_id,
                src_order_no, warehouse_no, warehouse_name, status, 
                order_type, receiver_name, receiver_mobile, 
                receiver_province, receiver_city, receiver_district, receiver_address,
                logistics_no, logistics_type, logistics_code, logistics_name,
                goods_count, goods_total_amount, post_fee, paid,shop_no, shop_name,
                created, modified, consign_time, trade_time, pay_time
            ) VALUES (
                %(stockout_id)s, %(order_no)s, %(trade_id)s, %(warehouse_id)s, %(logistics_id)s,
                %(src_order_no)s, %(warehouse_no)s, %(warehouse_name)s, %(status)s, 
                %(order_type)s, %(receiver_name)s, %(receiver_mobile)s, 
                %(receiver_province)s, %(receiver_city)s, %(receiver_district)s, %(receiver_address)s,
                %(logistics_no)s, %(logistics_type)s, %(logistics_code)s, %(logistics_name)s,
                %(goods_count)s, %(goods_total_amount)s, %(post_fee)s, %(paid)s, %(shop_no)s, %(shop_name)s,
                %(created)s, %(modified)s, %(consign_time)s, %(trade_time)s, %(pay_time)s
            ) ON DUPLICATE KEY UPDATE
                warehouse_id = VALUES(warehouse_id),
                logistics_id = VALUES(logistics_id),
                logistics_no = VALUES(logistics_no),
                status = VALUES(status),
                receiver_name = VALUES(receiver_name),
                receiver_mobile = VALUES(receiver_mobile),
                receiver_province = VALUES(receiver_province),
                receiver_city = VALUES(receiver_city),
                receiver_district = VALUES(receiver_district),
                receiver_address = VALUES(receiver_address),
                modified = VALUES(modified)
        """
        
        main_opera(sql, order_data, False)
    except Exception as e:
        raise e


def insert_stockout_order_detail(detail_data):
    for key in detail_data:
        if detail_data[key] is None:
            detail_data[key] = ''
        elif isinstance(detail_data[key], (list, dict)):
            detail_data[key] = str(detail_data[key])
    """插入出库单详情数据"""
    try:
        sql = """
                INSERT INTO stockout_order_details (
                    rec_id, stockout_id, spec_no, goods_no, 
                    goods_name, brand_no, brand_name, spec_name,
                    spec_code, num, sell_price, cost_price,
                    total_amount, weight, unit_name, created, modified
                ) VALUES (
                    %(rec_id)s, %(stockout_id)s, %(spec_no)s, %(goods_no)s,
                    %(goods_name)s, %(brand_no)s, %(brand_name)s, %(spec_name)s,
                    %(spec_code)s, %(num)s, %(sell_price)s, %(cost_price)s,
                    %(total_amount)s, %(weight)s, %(unit_name)s, %(created)s, %(modified)s
                ) ON DUPLICATE KEY UPDATE
                    num = VALUES(num),
                    sell_price = VALUES(sell_price),
                    total_amount = VALUES(total_amount),
                    modified = VALUES(modified)
        """
        main_opera(sql, detail_data, False)
    except Exception as e:
        raise e
def insert_stockout_positions(stockout_positions):

    for key in stockout_positions:
        if stockout_positions[key] is None:
            stockout_positions[key] = ''
        elif isinstance(stockout_positions[key], (list, dict)):
            stockout_positions[key] = str(stockout_positions[key])
    try:
        position_sql = """
                    INSERT INTO stockout_positions (
                        rec_id, stockout_id, stockout_order_detail_id,
                        position_id, position_no, position_goods_count,
                        batch_no, expire_date, num, modified
                    ) VALUES (
                        %(rec_id)s, %(stockout_id)s, %(stockout_order_detail_id)s,
                        %(position_id)s, %(position_no)s, %(position_goods_count)s,
                        %(batch_no)s, %(expire_date)s, %(num)s, %(modified)s
                    ) ON DUPLICATE KEY UPDATE
                        position_goods_count = VALUES(position_goods_count),
                        num = VALUES(num),
                        modified = VALUES(modified)
                    """
        main_opera(position_sql, stockout_positions, False)
    except Exception as e:
        raise e


def insert_sales_order(sales_data):


    for key in sales_data:
        if sales_data[key] is None:
            sales_data[key] = ''
        elif isinstance(sales_data[key], (list, dict)):
            sales_data[key] = str(sales_data[key])

    """插入销售订单数据"""
    try:
        sql = """INSERT INTO trade_orders (
            rec_id, platform_id, shop_id, shop_no, tid,
            process_status, trade_status, guarantee_mode, pay_status,
            delivery_term, pay_method, refund_status,
            trade_time, pay_time, to_deliver_time, end_time, modified, created,
            receivable, goods_amount, post_amount, other_amount, discount, paid,
            receiver_name, receiver_country, receiver_province, receiver_city,
            receiver_district, receiver_mobile, receiver_telno, receiver_address,
            receiver_area, buyer_nick, buyer_message, customer_no,
            remark, remark_flag, goods_count, order_count
        ) VALUES (
            %(rec_id)s, %(platform_id)s, %(shop_id)s, %(shop_no)s, %(tid)s,
            %(process_status)s, %(trade_status)s, %(guarantee_mode)s, %(pay_status)s,
            %(delivery_term)s, %(pay_method)s, %(refund_status)s,
            %(trade_time)s, %(pay_time)s, %(to_deliver_time)s, %(end_time)s, 
            %(modified)s, %(created)s,
            %(receivable)s, %(goods_amount)s, %(post_amount)s, %(other_amount)s, 
            %(discount)s, %(paid)s,
            %(receiver_name)s, %(receiver_country)s, %(receiver_province)s, %(receiver_city)s,
            %(receiver_district)s, %(receiver_mobile)s, %(receiver_telno)s, %(receiver_address)s,
            %(receiver_area)s, %(buyer_nick)s, %(buyer_message)s, %(customer_no)s,
            %(remark)s, %(remark_flag)s, %(goods_count)s, %(order_count)s
        ) ON DUPLICATE KEY UPDATE
            modified = VALUES(modified),
            process_status = VALUES(process_status),
            trade_status = VALUES(trade_status),
            refund_status = VALUES(refund_status),
            receivable = VALUES(receivable),
            paid = VALUES(paid),
            goods_count = VALUES(goods_count)
        """
        main_opera(sql, sales_data, False)
    except Exception as e:
        raise e
def insert_sales_order_goods(goods_data):
    
    for key in goods_data:
        if goods_data[key] is None:
            goods_data[key] = ''
        elif isinstance(goods_data[key], (list, dict)):
            goods_data[key] = str(goods_data[key])

    """插入销售订单数据"""
    try:
        sql = """
            INSERT INTO trade_order_goods (
            rec_id, platform_id, tid, oid, status,
            process_status, refund_status, order_type,
            goods_id, spec_id, goods_no, spec_no,
            goods_name, spec_name,
            num, price, total_amount, share_amount, paid,
            modified, created, remark
        ) VALUES (
            %(rec_id)s, %(platform_id)s, %(tid)s, %(oid)s, %(status)s,
            %(process_status)s, %(refund_status)s, %(order_type)s,
            %(goods_id)s, %(spec_id)s, %(goods_no)s, %(spec_no)s,
            %(goods_name)s, %(spec_name)s,
            %(num)s, %(price)s, %(total_amount)s, %(share_amount)s, %(paid)s,
            %(modified)s, %(created)s, %(remark)s
        ) ON DUPLICATE KEY UPDATE
            modified = VALUES(modified),
            status = VALUES(status),
            process_status = VALUES(process_status),
            refund_status = VALUES(refund_status),
            num = VALUES(num),
            price = VALUES(price),
            total_amount = VALUES(total_amount),
            paid = VALUES(paid)
        """
        main_opera(sql, goods_data, False)
    except Exception as e:
        raise e

def insert_logistics_sync(logistics_data):
    """插入物流同步数据"""
    try:
        sql = """INSERT INTO logistics_sync (
            logistics_id, trade_id, logistics_no, sync_status,
            logistics_company, receiver_name, receiver_mobile,
            receiver_province, receiver_city, receiver_district,
            receiver_address, created, modified
        ) VALUES (
            %(logistics_id)s, %(trade_id)s, %(logistics_no)s, %(sync_status)s,
            %(logistics_company)s, %(receiver_name)s, %(receiver_mobile)s,
            %(receiver_province)s, %(receiver_city)s, %(receiver_district)s,
            %(receiver_address)s, %(created)s, %(modified)s
        ) ON DUPLICATE KEY UPDATE
            sync_status = VALUES(sync_status),
            logistics_company = VALUES(logistics_company),
            receiver_name = VALUES(receiver_name),
            receiver_mobile = VALUES(receiver_mobile),
            receiver_address = VALUES(receiver_address),
            modified = VALUES(modified)
        """
        main_opera(sql, logistics_data, False)
    except Exception as e:
        raise e